Scrollable and Updatable ResultSet
In JDBC, you can create a scrollable and updatable ResultSet by specifying the result set type and concurrency mode when creating a Statement or PreparedStatement object.
To obtain a ResultSet that supports both scrolling and updating, you need to use the following constants from the ResultSet interface:
ResultSet.TYPE_SCROLL_INSENSITIVE (or TYPE_SCROLL_SENSITIVE): This makes the result set scrollable, allowing movement both forward and backward, as well as to specific rows.
ResultSet.CONCUR_UPDATABLE: This makes the result set updatable, allowing you to modify its data and have those changes reflected in the underlying database.
The default result set type is TYPE_FORWARD_ONLY (forward-only, not scrollable) and the default concurrency is CONCUR_READ_ONLY (read-only, not updatable).
Example 1
import java.sql.*;
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
//here, conn is a Connection Object
ResultSet rs = stmt.executeQuery("SELECT * FROM table_name");
Example 2
import java.sql.*;
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM table_name WHERE some_column = ?",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
With a scrollable result set, you can use various methods to move the cursor beyond just next():
- next(): Moves to the next row (forward only, default behavior).
- previous(): Moves to the previous row (backward).
- first(): Moves to the first row.
- last(): Moves to the last row.
- absolute(int row): Moves to a specific row number.
- relative(int rows): Moves the cursor a relative number of rows from the current position.
- beforeFirst(): Positions the cursor before the first row.
- afterLast(): Positions the cursor after the last row.